packages <- c(
'ggplot2','tidyverse','plotly','leaflet',
'shiny','shinyWidgets','shinydashboard',
'xts','forecast','TTR',
'DT','lubridate','RColorBrewer','scales','stopwords',
'tidytext','stringr','wordcloud','wordcloud2','scales','dplyr','rfm',
'SnowballC','textmineR','topicmodels','textclean','tm'
)
for (package in packages) {
if (!require(package, character.only = T, quietly = T)) {
install.packages(package)
library(package, character.only = T)
}
}
crm <- read_csv("CRM_interacions_table.csv")
gift <- read_csv("gift_transactions_table.csv")
video <- read_csv("video_email_data_table.csv")
constituent <- read_csv("constituent_profiles_table.csv")
# CRM Interaction Type
g <- crm %>%
group_by(CRM_INTERACTION_TYPE) %>%
summarise(Total = n()) %>%
select(CRM_INTERACTION_TYPE, Total) %>%
ggplot(aes(x = reorder(CRM_INTERACTION_TYPE,Total) ,y = Total)) +
geom_bar(stat = "identity",width = 0.5, fill='black') +
scale_y_continuous(labels = scales::comma) +
labs(x ="CRM Interaction Type", y = "Count") + coord_flip() +
theme(legend.text = element_text(size = 12),
legend.title = element_text(size = 12),
axis.title = element_text(size = 14),
axis.text = element_text(size = 12))
ggplotly(g)
crm <- crm %>%
mutate(Year = lubridate::year(CRM_INTERACTION_DATE),
Quarter = lubridate::quarter(CRM_INTERACTION_DATE),
Month = lubridate::month(CRM_INTERACTION_DATE, label = TRUE),
DOW = lubridate::wday(CRM_INTERACTION_DATE, label=TRUE))
crm_year <- crm %>%
group_by(Year, CRM_INTERACTION_TYPE) %>%
summarise(Total = n()) %>%
select(Year,CRM_INTERACTION_TYPE, Total)
g <- ggplot(crm_year, aes(as.factor(Year), Total, group=CRM_INTERACTION_TYPE, colour = CRM_INTERACTION_TYPE)) +
geom_line( linewidth=1) + theme_minimal() +
labs(x = "Year", y = "Total", color="CRM Interaction Type") +
scale_y_continuous(labels = comma) +
theme(legend.text = element_text(size = 10),
legend.title = element_text(size = 10),
axis.title = element_text(size = 10),
axis.text = element_text(size = 10),
axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)
crm %>%
group_by(Quarter, CRM_INTERACTION_TYPE) %>%
summarise(Total = n()) %>%
select(Quarter,CRM_INTERACTION_TYPE, Total) %>%
ggplot(aes(as.factor(Quarter), Total, group=CRM_INTERACTION_TYPE, colour = CRM_INTERACTION_TYPE)) +
geom_line( linewidth=1) + theme_minimal() +
labs(x = "Quarter", y = "Total", color="CRM Interaction Type") +
scale_y_continuous(labels = comma) +
theme(legend.text = element_text(size = 10),
legend.title = element_text(size = 10),
axis.title = element_text(size = 10),
axis.text = element_text(size = 10),
axis.text.x = element_text(angle = 0, hjust = 1))
crm %>%
group_by(Month, CRM_INTERACTION_TYPE) %>%
summarise(Total = n()) %>%
select(Month,CRM_INTERACTION_TYPE, Total) %>%
ggplot(aes(as.factor(Month), Total, group=CRM_INTERACTION_TYPE, colour = CRM_INTERACTION_TYPE)) +
geom_line( linewidth=1) + theme_minimal() +
labs(x = "Quarter", y = "Total", color="CRM Interaction Type") +
scale_y_continuous(labels = comma) +
theme(legend.text = element_text(size = 10),
legend.title = element_text(size = 10),
axis.title = element_text(size = 10),
axis.text = element_text(size = 10),
axis.text.x = element_text(angle = 0, hjust = 1))
crm %>%
group_by(DOW, CRM_INTERACTION_TYPE) %>%
summarise(Total = n()) %>%
select(DOW,CRM_INTERACTION_TYPE, Total) %>%
ggplot(aes(as.factor(DOW), Total, group=CRM_INTERACTION_TYPE, colour = CRM_INTERACTION_TYPE)) +
geom_line( linewidth=1) + theme_minimal() +
labs(x = "Day of Week", y = "Total", color="CRM Interaction Type") +
scale_y_continuous(labels = comma) +
theme(legend.text = element_text(size = 10),
legend.title = element_text(size = 10),
axis.title = element_text(size = 10),
axis.text = element_text(size = 10),
axis.text.x = element_text(angle = 0, hjust = 1))
left_join(gift,crm,by='CONSTITUENT_ID') %>%
group_by(CRM_INTERACTION_TYPE) %>%
summarise(Total = sum(AMOUNT)) %>%
select(CRM_INTERACTION_TYPE,Total) %>%
ggplot(aes(x = reorder(CRM_INTERACTION_TYPE,Total) ,y = Total)) +
geom_bar(stat = "identity",width = 0.5, fill='black') +
scale_y_continuous(labels = scales::comma) +
labs(x ="CRM Interaction Type", y = "Donations") + coord_flip() +
theme(legend.text = element_text(size = 12),
legend.title = element_text(size = 12),
axis.title = element_text(size = 14),
axis.text = element_text(size = 12))
gift <- gift %>%
mutate(Year = lubridate::year(GIFT_DATE),
Quarter = lubridate::quarter(GIFT_DATE),
Month = lubridate::month(GIFT_DATE, label = TRUE),
DOW = lubridate::wday(GIFT_DATE, label=TRUE))
g <- gift %>%
group_by(Year) %>%
summarise(Total = sum(AMOUNT)) %>%
select(Year, Total) %>%
na.omit() %>%
ggplot(aes(Year, Total)) +
geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
labs(x = "Year", y = "Total") +
scale_y_continuous(labels = comma) +
theme(legend.text = element_text(size = 10),
legend.title = element_text(size = 10),
axis.title = element_text(size = 10),
axis.text = element_text(size = 10),
axis.text.x = element_text(angle = 90, hjust = 1))
ggplotly(g)
g <- gift %>%
group_by(Quarter) %>%
summarise(Total = sum(AMOUNT)) %>%
select(Quarter, Total) %>%
na.omit() %>%
ggplot(aes(Quarter, Total)) +
geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
labs(x = "Quarter", y = "Total") +
scale_y_continuous(labels = comma) +
theme(legend.text = element_text(size = 10),
legend.title = element_text(size = 10),
axis.title = element_text(size = 10),
axis.text = element_text(size = 10),
axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)
g <- gift %>%
group_by(Month) %>%
summarise(Total = sum(AMOUNT)) %>%
select(Month, Total) %>%
na.omit() %>%
ggplot(aes(Month, Total)) +
geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
labs(x = "Month", y = "Total") +
scale_y_continuous(labels = comma) +
theme(legend.text = element_text(size = 10),
legend.title = element_text(size = 10),
axis.title = element_text(size = 10),
axis.text = element_text(size = 10),
axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)
Gift by Day of Week
g <- gift %>%
group_by(DOW) %>%
summarise(Total = sum(AMOUNT)) %>%
select(DOW, Total) %>%
na.omit() %>%
ggplot(aes(DOW, Total)) +
geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
labs(x = "Day of Week", y = "Total") +
scale_y_continuous(labels = comma) +
theme(legend.text = element_text(size = 10),
legend.title = element_text(size = 10),
axis.title = element_text(size = 10),
axis.text = element_text(size = 10),
axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)
video <- video %>%
mutate(Year = lubridate::year(SENT_DATE),
Quarter = lubridate::quarter(SENT_DATE),
Month = lubridate::month(SENT_DATE, label = TRUE),
DOW = lubridate::wday(SENT_DATE, label=TRUE))
g <- video %>%
group_by(Year) %>%
summarise(Total = sum(VIDEO_VIEWS)) %>%
select(Year, Total) %>%
na.omit() %>%
ggplot(aes(as.factor(Year), Total)) +
geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
labs(x = "Year", y = "Total") +
scale_y_continuous(labels = comma) +
theme(legend.text = element_text(size = 10),
legend.title = element_text(size = 10),
axis.title = element_text(size = 10),
axis.text = element_text(size = 10),
axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)
g <- video %>%
group_by(Quarter) %>%
summarise(Total = sum(VIDEO_VIEWS)) %>%
select(Quarter, Total) %>%
na.omit() %>%
ggplot(aes(Quarter, Total)) +
geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
labs(x = "Quarter", y = "Total") +
scale_y_continuous(labels = comma) +
theme(legend.text = element_text(size = 10),
legend.title = element_text(size = 10),
axis.title = element_text(size = 10),
axis.text = element_text(size = 10),
axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)
g <- video %>%
group_by(Month) %>%
summarise(Total = sum(VIDEO_VIEWS)) %>%
select(Month, Total) %>%
na.omit() %>%
ggplot(aes(Month, Total)) +
geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
labs(x = "Month", y = "Total") +
scale_y_continuous(labels = comma) +
theme(legend.text = element_text(size = 10),
legend.title = element_text(size = 10),
axis.title = element_text(size = 10),
axis.text = element_text(size = 10),
axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)
g <- video %>%
group_by(DOW) %>%
summarise(Total = sum(VIDEO_VIEWS)) %>%
select(DOW, Total) %>%
na.omit() %>%
ggplot(aes(DOW, Total)) +
geom_bar(stat = "identity",width = 0.5, fill='black') + theme_minimal() +
labs(x = "Day of the week", y = "Total") +
scale_y_continuous(labels = comma) +
theme(legend.text = element_text(size = 10),
legend.title = element_text(size = 10),
axis.title = element_text(size = 10),
axis.text = element_text(size = 10),
axis.text.x = element_text(angle = 0, hjust = 1))
ggplotly(g)
rfm_df <- gift %>%
select(CONSTITUENT_ID,GIFT_DATE,AMOUNT) %>%
na.omit()
names(rfm_df)[names(rfm_df) == 'CONSTITUENT_ID'] <- 'customer_id'
#rfm model setup
analysis_date <- lubridate::as_date(today(), tz = "UTC")
report <- rfm_table_order(rfm_df, customer_id,GIFT_DATE,AMOUNT, analysis_date)
#segment
segment_titles <- c("First Grade", "Loyal", "Likely to be Loyal",
"New Ones", "Could be Promising", "Require Assistance", "Getting Less Frequent",
"Almost Out", "Can't Lose Them", "Don’t Show Up at All")
#numerical thresholds
r_low <- c(4, 2, 3, 4, 3, 2, 2, 1, 1, 1)
r_high <- c(5, 5, 5, 5, 4, 3, 3, 2, 1, 2)
f_low <- c(4, 3, 1, 1, 1, 2, 1, 2, 4, 1)
f_high <- c(5, 5, 3, 1, 1, 3, 2, 5, 5, 2)
m_low <- c(4, 3, 1, 1, 1, 2, 1, 2, 4, 1)
m_high <- c(5, 5, 3, 1, 1, 3, 2, 5, 5, 2)
divisions<-rfm_segment(report, segment_titles, r_low, r_high, f_low, f_high, m_low, m_high)
division_count <- divisions %>% count(segment) %>% arrange(desc(n)) %>% rename(Segment = segment, Count = n)
rfm_plot_heatmap(report)
rfm_plot_bar_chart(report)
rfm_plot_histogram(report)
# Calculate average donation per customer
avg_donation_per_customer <- gift %>%
group_by(CONSTITUENT_ID) %>%
summarize(avg_revenue = mean(AMOUNT))
# Calculate average donor lifespan (simplified, using the number of months)
avg_donor_lifespan <- gift %>%
group_by(CONSTITUENT_ID) %>%
summarize(avg_lifespan = as.numeric(difftime(max(GIFT_DATE), min(GIFT_DATE), units = "days"))) %>%
na.omit()
# Calculate CLV
clv_df <- inner_join(avg_donation_per_customer,avg_donor_lifespan,by='CONSTITUENT_ID') %>%
group_by(CONSTITUENT_ID) %>%
mutate(CLV_calc = avg_revenue * avg_lifespan) %>%
select(CONSTITUENT_ID,CLV_calc)
#clv <- avg_revenue_per_customer$avg_revenue * avg_lifespan$avg_lifespan
#print(clv)